﻿create procedure PopulateKeyPersonnelFieldValueDistributionStats 
(@TableName varchar(255),
@FieldName varchar(255),
@FieldDbType varchar(50),
@FieldValueDistributionTableName varchar(255)) 
as
begin
	declare @BinCount int;
	set @BinCount=4;
	-- create table, delete pre-existing table  
	declare @DropTableSql nvarchar(2000);
	set @DropTableSql=N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('''+
		@FieldValueDistributionTableName + ''') AND type in (N''U''))
		DROP TABLE '+@FieldValueDistributionTableName+';';
	exec sp_executesql @DropTableSql;
	declare @CreateTableSql nvarchar(2000);
	set @CreateTableSql=N'Create Table '+@FieldValueDistributionTableName+ '('+
		@FieldName + ' '+ @FieldDbType + ' primary key,'+
		'Frequency int, '+
		'Ratio float, '+
		'LogRatio float)';
	exec sp_executesql @CreateTableSql;
	
	-- populate value distribution
	declare @ParamDefinition nvarchar(500);
	set @ParamDefinition=N'@ReturnValue int output';
	declare @DynamicSql nvarchar(4000);
	declare @FrequencyThreshold int;
	set @DynamicSql=N'
		select @ReturnValue=T2.Frequency from 
		(
			select 
				T1.Frequency, ROW_NUMBER() over (order by T1.Frequency desc) as Rank 
			from 
			(
				select Count ('+@FieldName+') as Frequency 
				from '+@TableName+' 
				where '+@FieldName+' is not null 
				group by '+@FieldName+' 
			) T1
		) T2 
		where T2.Rank=(select (select COUNT (distinct '+@FieldName+') 
		from '+@TableName+' where '+@FieldName+' is not null)/'+
		cast(@BinCount as varchar) + ')';
	exec sp_executesql @DynamicSql, @ParamDefinition, @ReturnValue=@FrequencyThreshold output;
	
	set @ParamDefinition=N'@FrequencyThreshold int';
	set @DynamicSql=N'insert into '+@FieldValueDistributionTableName+
		'('+@FieldName+', Frequency, Ratio, LogRatio)  
			select 
			'+@FieldName+',
			count ('+@FieldName+') as Frequency,
			case 
				when(count('+@FieldName+')>@FrequencyThreshold) then 1 
				else cast(count('+@FieldName+') as float)/@FrequencyThreshold  
			end as Ratio,
			case 
				when (count ('+@FieldName+'))>@FrequencyThreshold then 0 
				else 0-LOG10((cast(count('+@FieldName+') as float))/@FrequencyThreshold)
			end as LogRatio 
		from '+@TableName+' 
		where '+@FieldName+' is not null 
		group by '+@FieldName;
	exec sp_executesql @DynamicSql, @ParamDefinition, @FrequencyThreshold;
end 